package com.iamteer.day18.task;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Savepoint;

import org.junit.Test;

public class AccountDao {
	
	private Connection con;
	private PreparedStatement pstmt;
	
	private String url = "jdbc:mysql:///day18";
	private String user = "root";
	private String password = "123456";	
	
	//1 转账，没有使用事务
	@Test
	public void transfer1() {
		String sql_in = "UPDATE account SET money=money+1000 WHERE accountName='李四'";
		String sql_out = "UPDATE account SET money=money-1000 WHERE accountName='张三'";
		
		try {
			//连接数据库
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(url, user, password);
			con.setAutoCommit(true);	//默认就是 true
			
			//从转出账户扣钱
			pstmt = con.prepareStatement(sql_out);
			pstmt.executeUpdate();
			
			//给转入账户加钱——如果这里执行不成功，会出现扣了钱，但没加钱的严重问题！
			pstmt = con.prepareStatement(sql_in);
			pstmt.executeUpdate();
			
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}
	
	//2 转账，使用事务
	@Test
	public void transfer2() {
		String sql_in = "UPDATE account SET money=money+1000 WHERE accountName='李四'";
		String sql_out = "UPDATE account SET money=money-1000 WHERE accountName='张三'";
		
		try {
			//连接数据库
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(url, user, password);
			con.setAutoCommit(false);	//设置手动提交
			
			//从转出账户扣钱
			pstmt = con.prepareStatement(sql_out);
			pstmt.executeUpdate();
			//给转入账户加钱
			pstmt = con.prepareStatement(sql_in);
			pstmt.executeUpdate();
			
		} catch (Exception e) {
			try {
				//出现异常，需要回滚事务
				con.rollback();
			} catch (SQLException e1) {
			}
			throw new RuntimeException(e);
		} finally {
			try {
				//提交事务：成功是执行，否则是回滚
				con.commit();
				
				pstmt.close();
				con.close();
			} catch (Exception e) {
				throw new RuntimeException(e);
			}
		}		
	}
	
	//3  多次转账，使用事务回滚到指定地方
	@Test
	public void transfer3() {
		String sql_in1 = "UPDATE account SET money=money+1000 WHERE accountName='李四'";
		String sql_out1 = "UPDATE account SET money=money-1000 WHERE accountName='张三'";
		
		String sql_in2 = "UPDATE account SET money=money+500 WHERE accountName='李四'";
		String sql_out2 = "UPDATEf account SET money=money-500 WHERE accountName='张三'";
		
		Savepoint sp = null;
		try {
			//连接数据库
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(url, user, password);
			con.setAutoCommit(false);	//设置手动提交
			
			//第一次转账
			//从转出账户扣钱
			pstmt = con.prepareStatement(sql_out1);
			pstmt.executeUpdate();
			//给转入账户加钱
			pstmt = con.prepareStatement(sql_in1);
			pstmt.executeUpdate();
			
			//回滚到这个位置
			sp = con.setSavepoint();
			
			//第二次转账
			//从转出账户扣钱
			pstmt = con.prepareStatement(sql_out2);
			pstmt.executeUpdate();
			//给转入账户加钱
			pstmt = con.prepareStatement(sql_in2);
			pstmt.executeUpdate();			
			
		} catch (Exception e) {
			try {
				//出现异常，需要回滚事务
				con.rollback(sp);
			} catch (SQLException e1) {
			}
			throw new RuntimeException(e);
		} finally {
			try {
				//提交事务：成功是执行，否则是回滚
				con.commit();
				
				pstmt.close();
				con.close();
			} catch (Exception e) {
				throw new RuntimeException(e);
			}
		}		
	}
	
}
